{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.svm import SVR\n",
    "from sklearn.ensemble import GradientBoostingRegressor\n",
    "import matplotlib.pyplot as plt\n",
    "from openpyxl import Workbook\n",
    "\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei'] \n",
    "plt.rcParams['axes.unicode_minus'] = False  \n",
    "\n",
    "# 1. 读取训练数据\n",
    "train_data = pd.read_excel('合并数据集.xlsx')\n",
    "\n",
    "# 提取所需特征和目标变量\n",
    "material = pd.Categorical(train_data['材料']).codes  #\n",
    "temperature = train_data['温度，oC']                 \n",
    "frequency = train_data['频率，Hz']                  \n",
    "core_loss = train_data['磁芯损耗，w/m3']            \n",
    "waveform = pd.Categorical(train_data['励磁波形']).codes  \n",
    "\n",
    "# 提取磁通密度峰值（假设磁通密度从第6列到最后列）\n",
    "magnetic_density = train_data.iloc[:, 5:].values   \n",
    "Bm_train = np.max(magnetic_density, axis=1)          \n",
    "\n",
    "# 构建特征矩阵（输入变量），加入磁通密度峰值\n",
    "X_train = np.column_stack([material, temperature, frequency, waveform, Bm_train])\n",
    "\n",
    "# 2. 线性回归模型\n",
    "linear_model = LinearRegression()\n",
    "linear_model.fit(X_train, core_loss)\n",
    "linear_pred_train = linear_model.predict(X_train)\n",
    "\n",
    "# 3. 支持向量机回归（SVR）模型\n",
    "svr_model = SVR(kernel='rbf', C=100, gamma=0.1)\n",
    "svr_model.fit(X_train, core_loss)\n",
    "svr_pred_train = svr_model.predict(X_train)\n",
    "\n",
    "# 4. 梯度提升回归模型\n",
    "gbr_model = GradientBoostingRegressor(n_estimators=100, learning_rate=0.1, max_depth=3, random_state=1)\n",
    "gbr_model.fit(X_train, core_loss)\n",
    "gbr_pred_train = gbr_model.predict(X_train)\n",
    "\n",
    "# 输出各模型的 R²得分\n",
    "linear_score = linear_model.score(X_train, core_loss)\n",
    "svr_score = svr_model.score(X_train, core_loss)\n",
    "gbr_score = gbr_model.score(X_train, core_loss)\n",
    "\n",
    "print(f'线性回归模型的R²得分: {linear_score}')\n",
    "print(f'SVR模型的R²得分: {svr_score}')\n",
    "print(f'梯度提升模型的R²得分: {gbr_score}')\n",
    "\n",
    "# 读取测试数据\n",
    "test_data = pd.read_excel('附件三（测试集）.xlsx')\n",
    "\n",
    "# 提取测试数据中的特征\n",
    "material_test = pd.Categorical(test_data['磁芯材料']).codes   # 材料：材料1、材料2、材料3、材料4\n",
    "temperature_test = test_data['温度，oC']                      # 温度\n",
    "frequency_test = test_data['频率，Hz']                        # 频率\n",
    "waveform_test = pd.Categorical(test_data['励磁波形']).codes   # 励磁波形\n",
    "\n",
    "# 提取测试数据中的磁通密度峰值（假设磁通密度从第6列到最后列）\n",
    "magnetic_density_test = test_data.iloc[:, 5:].values      # 提取磁通密度数据\n",
    "Bm_test = np.max(magnetic_density_test, axis=1)           # 提取每行（每个样本）的磁通密度峰值\n",
    "\n",
    "# 构建测试数据的特征矩阵，加入磁通密度峰值\n",
    "X_test = np.column_stack([material_test, temperature_test, frequency_test, waveform_test, Bm_test])\n",
    "\n",
    "# 预测测试集结果\n",
    "linear_pred = linear_model.predict(X_test)\n",
    "svr_pred = svr_model.predict(X_test)\n",
    "gbr_pred = gbr_model.predict(X_test)\n",
    "\n",
    "# 5. 结果的加权平均（根据模型的 R² 得分）\n",
    "# 加权系数可以根据各个模型的 R² 得分来决定\n",
    "total_score = linear_score + svr_score + gbr_score\n",
    "linear_weight = linear_score / total_score\n",
    "svr_weight = svr_score / total_score\n",
    "gbr_weight = gbr_score / total_score\n",
    "\n",
    "weighted_pred = (linear_weight * linear_pred + svr_weight * svr_pred + gbr_weight * gbr_pred)\n",
    "\n",
    "# 6. 保存预测结果为 Excel 文件\n",
    "predicted_core_loss_table = pd.DataFrame({\n",
    "    'LinearRegression': linear_pred,\n",
    "    'SVR': svr_pred,\n",
    "    'GradientBoosting': gbr_pred,\n",
    "    'WeightedPrediction': weighted_pred\n",
    "})\n",
    "\n",
    "# 保存到 Excel 文件\n",
    "predicted_core_loss_table.to_excel('问题四加权预测磁芯损耗结果.xlsx', index=False)\n",
    "print('预测结果已成功保存为 问题四加权预测磁芯损耗结果.xlsx')\n",
    "\n",
    "# 7. 可视化各个模型和加权模型的预测结果\n",
    "plt.figure(figsize=(10, 6))\n",
    "plt.plot(linear_pred, label='线性回归', linestyle='--')\n",
    "plt.plot(svr_pred, label='SVR', linestyle='-.')\n",
    "plt.plot(gbr_pred, label='梯度提升回归', linestyle=':')\n",
    "plt.plot(weighted_pred, label='加权预测', linestyle='-', color='r', linewidth=2)\n",
    "plt.xlabel('样本编号')\n",
    "plt.ylabel('预测磁芯损耗 (W/m^3)')\n",
    "plt.title('磁芯损耗预测结果 - 不同模型与加权结果')\n",
    "plt.legend()\n",
    "plt.grid(True)\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor\n",
    "from sklearn.svm import SVR\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.impute import SimpleImputer\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.model_selection import GridSearchCV\n",
    "from sklearn.metrics import mean_squared_error, r2_score\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "from sklearn.impute import SimpleImputer\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.metrics import mean_squared_error, r2_score\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from scipy.fftpack import fft\n",
    "from scipy.signal import find_peaks\n",
    "from scipy.stats import skew, kurtosis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 设置全局字体，避免中文乱码\n",
    "plt.rcParams['figure.dpi'] = 300  # 提高所有图形的分辨率\n",
    "config = {\n",
    "    \"font.family\": 'serif',\n",
    "    \"font.size\": 16,\n",
    "    \"mathtext.fontset\": 'stix',\n",
    "    \"font.serif\": ['SimSun'],  # 使用宋体，前提是系统安装了该字体\n",
    "}\n",
    "plt.rcParams.update(config)  # 更新配置\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. 数据读取\n",
    "file_path_train = '聚合后的材料数据.xlsx'\n",
    "file_path_test = '附件三（测试集）.xlsx'\n",
    "file_result = '附件四（Excel表）.xlsx'\n",
    "\n",
    "# 读取训练集数据\n",
    "df_train = pd.read_excel(file_path_train)\n",
    "\n",
    "# 读取测试集数据\n",
    "df_test = pd.read_excel(file_path_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. 特征提取\n",
    "# a. 时域特征提取\n",
    "def extract_time_domain_features(data):\n",
    "    features = []\n",
    "    for row in data:\n",
    "        mean_val = np.mean(row)\n",
    "        std_val = np.std(row)\n",
    "        max_val = np.max(row)\n",
    "        min_val = np.min(row)\n",
    "        slope = (row[-1] - row[0]) / len(row)  # 斜率\n",
    "        peaks, _ = find_peaks(row)\n",
    "        num_peaks = len(peaks)\n",
    "        smoothness = np.sum(np.abs(np.diff(row)))  # 平滑度\n",
    "        skewness = skew(row)  # 偏度\n",
    "        kurt = kurtosis(row)  # 峰度\n",
    "        features.append([mean_val, std_val, max_val, min_val, slope, num_peaks, smoothness, skewness, kurt])\n",
    "    return np.array(features)\n",
    "\n",
    "# b. 频域特征提取\n",
    "def extract_frequency_domain_features(data):\n",
    "    features = []\n",
    "    for row in data:\n",
    "        fft_values = fft(row)\n",
    "        fft_amplitude = np.abs(fft_values)\n",
    "        fft_main_freq = np.argmax(fft_amplitude)\n",
    "        high_freq_energy_ratio = np.sum(fft_amplitude[1:10]) / np.sum(fft_amplitude)\n",
    "        spectral_entropy = -np.sum((fft_amplitude / np.sum(fft_amplitude)) * np.log(fft_amplitude / np.sum(fft_amplitude)))\n",
    "        power_spectral_density = np.sum(np.square(fft_amplitude))  # 功率谱密度\n",
    "        features.append([fft_main_freq, high_freq_energy_ratio, spectral_entropy, power_spectral_density])\n",
    "    return np.array(features)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3. 对训练集提取时域和频域特征\n",
    "flux_data_train = df_train.iloc[:, 5:].values\n",
    "time_features_train = extract_time_domain_features(flux_data_train)\n",
    "freq_features_train = extract_frequency_domain_features(flux_data_train)\n",
    "\n",
    "# 4. 处理训练集数据\n",
    "df_time_freq_train = pd.DataFrame(np.hstack([time_features_train, freq_features_train]),\n",
    "                                  columns=['均值', '标准差', '最大值', '最小值', '斜率', '波峰数', '平滑度', '偏度', '峰度',\n",
    "                                           '主频率', '高频能量比', '谱熵', '功率谱密度'])\n",
    "df_time_freq_train['温度'] = df_train['温度，oC']\n",
    "df_time_freq_train['频率'] = df_train['频率，Hz']\n",
    "df_time_freq_train['磁芯损耗'] = df_train['磁芯损耗，w/m3']\n",
    "\n",
    "# 5. 对测试集提取时域和频域特征\n",
    "flux_data_test = df_test.iloc[:, 5:].values\n",
    "time_features_test = extract_time_domain_features(flux_data_test)\n",
    "freq_features_test = extract_frequency_domain_features(flux_data_test)\n",
    "\n",
    "df_time_freq_test = pd.DataFrame(np.hstack([time_features_test, freq_features_test]),\n",
    "                                 columns=['均值', '标准差', '最大值', '最小值', '斜率', '波峰数', '平滑度', '偏度', '峰度',\n",
    "                                          '主频率', '高频能量比', '谱熵', '功率谱密度'])\n",
    "df_time_freq_test['温度'] = df_test['温度，oC']\n",
    "df_time_freq_test['频率'] = df_test['频率，Hz']\n",
    "\n",
    "# 确保训练集和测试集的特征顺序一致\n",
    "X_train = df_time_freq_train.drop(columns=['磁芯损耗'])\n",
    "y_train = df_time_freq_train['磁芯损耗']\n",
    "\n",
    "X_test = df_time_freq_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 6. 处理缺失值和标准化\n",
    "imputer = SimpleImputer(strategy='mean')\n",
    "scaler = StandardScaler()\n",
    "\n",
    "# 对训练集处理缺失值并标准化\n",
    "X_train_imputed = imputer.fit_transform(X_train)\n",
    "X_train_scaled = scaler.fit_transform(X_train_imputed)\n",
    "\n",
    "# 对测试集处理缺失值并标准化\n",
    "X_test_imputed = imputer.transform(X_test)\n",
    "X_test_scaled = scaler.transform(X_test_imputed)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. 线性回归模型\n",
    "linear_model = LinearRegression()\n",
    "linear_model.fit(X_train_scaled, y_train)\n",
    "linear_pred_train = linear_model.predict(X_train_scaled)\n",
    "\n",
    "# 3. 支持向量机回归（SVR）模型\n",
    "svr_model = SVR(kernel='rbf')\n",
    "svr_model.fit(X_train_scaled, y_train)\n",
    "svr_pred_train = svr_model.predict(X_train_scaled)\n",
    "\n",
    "# 4. 梯度提升回归模型\n",
    "gbr_model = GradientBoostingRegressor(n_estimators=100, learning_rate=0.1, max_depth=3, random_state=1)\n",
    "gbr_model.fit(X_train_scaled, y_train)\n",
    "gbr_pred_train = gbr_model.predict(X_train_scaled)\n",
    "\n",
    "# 5. 随机森林模型\n",
    "rf_model = RandomForestRegressor(n_estimators=100, random_state=42)\n",
    "rf_model.fit(X_train_scaled, y_train)\n",
    "\n",
    "# 6. 超参数优化 - 网格搜索\n",
    "# 定义要调整的参数\n",
    "param_grid_svr = {'C': [1, 10, 100], 'gamma': [0.01, 0.1, 1]}\n",
    "param_grid_gbr = {'n_estimators': [100, 200], 'learning_rate': [0.05, 0.1], 'max_depth': [3, 5]}\n",
    "\n",
    "# 支持向量机回归模型优化\n",
    "svr_grid_search = GridSearchCV(SVR(kernel='rbf'), param_grid_svr, cv=5, scoring='r2')\n",
    "svr_grid_search.fit(X_train_scaled, y_train)\n",
    "best_svr = svr_grid_search.best_estimator_\n",
    "print(\"最佳SVR参数:\", svr_grid_search.best_params_)\n",
    "\n",
    "# 梯度提升回归模型优化\n",
    "gbr_grid_search = GridSearchCV(GradientBoostingRegressor(random_state=1), param_grid_gbr, cv=5, scoring='r2')\n",
    "gbr_grid_search.fit(X_train_scaled, y_train)\n",
    "best_gbr = gbr_grid_search.best_estimator_\n",
    "print(\"最佳GBR参数:\", gbr_grid_search.best_params_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "param_grid_rf = {\n",
    "    'n_estimators': [100, 200, 300],  # 森林中的树木数量\n",
    "    'max_depth': [10, 20, None],  # 每棵树的最大深度\n",
    "    'min_samples_split': [2, 5, 10],  # 分裂节点的最小样本数\n",
    "    'min_samples_leaf': [1, 2, 4]  # 叶子节点的最小样本数\n",
    "}\n",
    "\n",
    "rf_grid_search = GridSearchCV(RandomForestRegressor(random_state=42), param_grid_rf, cv=5, scoring='r2')\n",
    "rf_grid_search.fit(X_train_scaled, y_train)\n",
    "best_rf = rf_grid_search.best_estimator_  # 获取最佳的随机森林模型\n",
    "print(\"最佳随机森林参数:\", rf_grid_search.best_params_)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import RandomizedSearchCV\n",
    "\n",
    "# 随机森林参数空间\n",
    "param_dist_rf = {\n",
    "    'n_estimators': [100, 200, 300],  # 森林中树木的数量\n",
    "    'max_depth': [10, 20, None],  # 最大树深度\n",
    "    'min_samples_split': [2, 5, 10],  # 分裂节点的最小样本数\n",
    "    'min_samples_leaf': [1, 2, 4],  # 叶子节点的最小样本数\n",
    "    'bootstrap': [True, False]  # 是否采用自助采样法\n",
    "}\n",
    "\n",
    "# 使用 RandomizedSearchCV 进行超参数搜索，设定 n_iter 控制搜索次数\n",
    "rf_random_search = RandomizedSearchCV(RandomForestRegressor(random_state=42), param_distributions=param_dist_rf,\n",
    "                                      n_iter=50, cv=5, verbose=2, random_state=42, n_jobs=-1, scoring='r2')\n",
    "\n",
    "rf_random_search.fit(X_train_scaled, y_train)\n",
    "\n",
    "# 获取最佳随机森林模型\n",
    "best_rf = rf_random_search.best_estimator_\n",
    "print(\"最佳随机森林参数:\", rf_random_search.best_params_)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#模型对比\n",
    "models = {\n",
    "    '线性回归': linear_model,\n",
    "    '支持向量机回归': best_svr,\n",
    "    '梯度提升回归': best_gbr,\n",
    "    '随机森林回归': best_rf  # 使用最佳的随机森林模型\n",
    "}\n",
    "\n",
    "# 保存结果\n",
    "results = {}\n",
    "\n",
    "for model_name, model in models.items():\n",
    "    # 预测训练集\n",
    "    y_train_pred = model.predict(X_train_scaled)\n",
    "    \n",
    "    # 计算性能指标\n",
    "    rmse = np.sqrt(mean_squared_error(y_train, y_train_pred))\n",
    "    mse = mean_squared_error(y_train, y_train_pred)\n",
    "    r2 = r2_score(y_train, y_train_pred)\n",
    "    \n",
    "    results[model_name] = {'R2': r2, 'MSE': mse, 'RMSE': rmse}\n",
    "\n",
    "# 输出结果\n",
    "print(\"\\n模型性能对比：\")\n",
    "results_df = pd.DataFrame(results).T\n",
    "print(results_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8. 绘制对比图\n",
    "results_df.plot(kind='bar', figsize=(10, 6), title=\"模型性能对比（R², MSE, RMSE）\")\n",
    "plt.ylabel('值')\n",
    "plt.grid(True)\n",
    "plt.show()\n",
    "\n",
    "# 9. 使用最佳模型进行预测\n",
    "best_model_name = results_df['R2'].idxmax()  # 选择 R2 最高的模型\n",
    "print(f\"最佳模型为: {best_model_name}\")\n",
    "\n",
    "best_model = models[best_model_name]\n",
    "y_test_pred = best_model.predict(X_test_scaled).round(1)\n",
    "\n",
    "# 10. 保存预测结果到Excel\n",
    "df_result = pd.read_excel(file_result)\n",
    "df_result['磁芯损耗预测结果'] = y_test_pred\n",
    "df_result.to_excel('附件四_预测结果_最佳模型.xlsx', index=False)\n",
    "print(\"最佳模型的预测结果已保存至 '附件四_预测结果_最佳模型.xlsx'\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py38torch",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
